package com.aliyun.heiyu.powermaster.server.rotary.utils;

import com.aliyun.heiyu.common.source.ConnectionManager;
import org.apache.commons.beanutils.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
    private static final Logger logger = LoggerFactory.getLogger(JdbcUtil.class);
    private static final ConnectionManager connectionManager = ConnectionManager.getInstance();
    private static boolean settingParams(PreparedStatement preparedStatement, Object[] param) throws SQLException {
        if (param != null && param.length > 0) {
            // 获取ParameterMetaData
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            // 获得SQL中占位符个数
            int paramCount = parameterMetaData.getParameterCount();

            // 占位符个数与参数个数不一致，返回false表示出错
            if (paramCount != param.length) {
                return false;
            }
            // 设置对应的参数信息
            for (int i = 0; i < paramCount; i++) {
                preparedStatement.setObject(i + 1, param[i]);
            }
        }
        return true;
    }

    public static <T> T queryForBean(String sql, Object[] param, Class<T> clazz) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
            connection = connectionManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false) {
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null) {
                return null;
            }

            if (resultSet.next()) {
                // 利用反射机制创建对象
                T data = clazz.newInstance();
                // 获得ResultSetMetaData
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                // 获得列的数量
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    // 获得对应的列的名称
                    String name = resultSetMetaData.getColumnName(i + 1);
                    // 获得对应的列的值
                    Object rData = resultSet.getObject(name);
                    // 使用BeanUtils工具对属性进行注入
                    BeanUtils.copyProperty(data, name, rData);
                }
                return data;

            } else {
                return null;
            }

        } catch (InstantiationException e) {
            logger.error(e.getMessage(), e);
        } catch (IllegalAccessException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } catch (InvocationTargetException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(preparedStatement != null){
                close(preparedStatement);
            }
            if(resultSet != null){
                close(resultSet);
            }
            if(connection != null){
                close(connection);
            }
        }
        return null;
    }

    public static <T> List<T> queryForList(String sql, Object[] param, Class<T> clazz) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
            connection = connectionManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false) {
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null) {
                return null;
            }
            List<T> results = new ArrayList<>();

            while (resultSet.next()) {
                // 创建对象
                T data = clazz.newInstance();
                // 获得ResultSetMetaData
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                // 获得列的数量
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    // 获得对应的列的名称
                    String name = resultSetMetaData.getColumnName(i + 1);
                    // 获得对应的列的值
                    Object rData = resultSet.getObject(name);
                    // 使用BeanUtils工具对属性进行注入
                    BeanUtils.copyProperty(data, name, rData);
                }
                results.add(data);

            }
            return results;

        } catch (InstantiationException e) {
            logger.error(e.getMessage(), e);
        } catch (IllegalAccessException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } catch (InvocationTargetException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(preparedStatement != null){
                close(preparedStatement);
            }
            if(resultSet != null){
                close(resultSet);
            }
            if(connection != null){
                close(connection);
            }
        }
        return null;
    }

    public static Map<String, String> queryForMap(String sql, Object[] param) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
            connection = connectionManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false) {
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null) {
                return null;
            }

            if (resultSet.next()) {
                // 利用反射机制封装Map
                Map<String, String> map = new HashMap<String, String>(16);
                // 获得ResultSetMetaData
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                // 获得列的数量
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    // 获得对应的列的名称
                    String name = resultSetMetaData.getColumnName(i + 1);
                    // 获得对应的列的值
                    Object rData = resultSet.getObject(name);
                    String s = String.valueOf(rData);
                    if (rData != null) {
                        //rData = "";
                        map.put(name, s);
                    }

                }
                return map;

            } else {
                return null;
            }

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(preparedStatement != null){
                close(preparedStatement);
            }
            if(resultSet != null){
                close(resultSet);
            }
            if(connection != null){
                close(connection);
            }
        }
        return null;
    }

    public static List<Map<String, String>> queryForListMap(String sql, Object[] param) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
        try {
            connection = connectionManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false) {
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null) {
                return null;
            }

            while (resultSet.next()) {
                // 利用反射机制封装Map
                Map<String, String> map = new HashMap<String, String>(16);
                // 获得ResultSetMetaData
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                // 获得列的数量
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    // 获得对应的列的名称
                    String name = resultSetMetaData.getColumnName(i + 1);
                    // 获得对应的列的值
                    Object rData = resultSet.getObject(name);
                    String s = String.valueOf(rData);
                    if (rData != null) {
                        //rData = "";
                        map.put(name, s);
                    }

                }
                mapList.add(map);

            }
            return mapList;

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(preparedStatement != null){
                close(preparedStatement);
            }
            if(resultSet != null){
                close(resultSet);
            }
            if(connection != null){
                close(connection);
            }
        }
        return null;
    }

    public static boolean update(String sql, Object[] param) {
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        try {
            connection = connectionManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false) {
                return false;
            }

            int result = preparedStatement.executeUpdate();
            if (result > 0) {
                return true;
            }
            return false;

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(preparedStatement != null){
                close(preparedStatement);
            }
            if(connection != null){
                close(connection);
            }
        }
        return false;
    }

    /**
     * 插入，返回插入后的自增ID值
     */
    public static Integer insert(String sql, Object[] param) {
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        ResultSet generatedKeys = null;
        Integer insertID = null;
        try {
            connection = connectionManager.getConnection();
            preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

            if (settingParams(preparedStatement, param) == false) {
                throw new RuntimeException("settingParams failed!");
            }

            int result = preparedStatement.executeUpdate();
            if (result <= 0) {
                throw new RuntimeException("insert failed!");
            }
            generatedKeys = preparedStatement.getGeneratedKeys();
            if(generatedKeys == null){
                return null;
            }
            if (generatedKeys.next()) {
                insertID = generatedKeys.getInt(1);
            }

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if(preparedStatement != null){
                close(preparedStatement);
            }
            if(generatedKeys != null){
                close(generatedKeys);
            }
            if(connection != null){
                close(connection);
            }
        }
        return insertID;
    }


    private static void close(Connection connection) {
        if(connection != null){
            try {
                connectionManager.close(connection);
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }


    private static void close(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    private static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }
}
